<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "DTD/xhtml1-transitional.dtd">















 
 
 
 
 
 
 
  
  


<html>
  <head>
    <script type="text/javascript" language="JavaScript">
    ORIGINAL_PAGE_PATH = "/appengine/docs/python/datastore/queriesandindexes.html";
    </script>
    
    
<meta http-equiv="content-type" content="text/html; charset=utf-8"/>
<title>Queries and Indexes - Google App Engine - Google Code</title>
<script type="text/javascript"><!--
(function(){function a(){this.t={};this.tick=function(c){this.t[c]=(new Date).getTime()};this.tick("start")}var b=new a;window.jstiming={Timer:a,load:b};if(window.external&&window.external.pageT)window.jstiming.pt=window.external.pageT;})();

var _tocPath_ = '/appengine/docs/_toc.ezt';
var codesite_token = null;
//--></script>
<link href="../../../../css/codesite.pack.04102009.css" type="text/css" rel="stylesheet"></link>
<script src="../../../../js/codesite_head.pack.04102009.js" type="text/javascript"></script>
<script type="text/javascript">CODESITE_CSITimer['load'].tick('bhs');</script>
<link rel="search" type="application/opensearchdescription+xml" title="Google Code" href="http://code.google.com/osd.xml" />

<!--[if IE]><link rel="stylesheet" type="text/css" href="../../../../css/iehacks.css" /><![endif]-->

    <link href="../../../../css/semantic_headers.css" rel="stylesheet" type="text/css" />
    <link href="../../../css/local_extensions.css" rel="stylesheet" type="text/css" />
  </head>

  <body class="gc-documentation">

    
    
    
</a>

<div id="gb">
 <span>
  
    <a id="lang-dropdown" class="dropdown" href="http://code.google.com" onclick="return false;"><img class="globeicon" src="../../../../images/globe2_small.png"/><span>English</span></a>
  
 </span>
</div>

<div class="gbh" style="left: 0pt;"></div>
<div class="gbh" style="right: 0pt;"></div>


<style type="text/css">
  #gc-topnav h1 {
    padding: 0 0 0 6px;
  }
</style>


<div id="gc-container">
<a name="top"></a>
<div id="skipto">
  <a href="#gc-pagecontent">Skip to page content</a>
  <a href="#gc-toc">Skip to main navigation</a>
</div>

<div id="gc-header">
  <div id="logo"><a href="http://code.google.com">
  
  
     <img src="../../../../images/cleardot.gif" height="1px" width="1px" alt="Google Code Home Page" id="gc-logo-img"/>
  
  
  </a></div>
  <div id="search">
    <div id="searchForm" class="searchForm">
      <form id="cse" action="http://www.google.com/cse" accept-charset="utf-8" class="gsc-search-box" onsubmit="executeGSearch(document.getElementById('gsearchInput').value); return false;">
      <noscript>
      <input type="hidden" name="cref" value="http://code.google.com/cse/googlecode-context.xml"/>
      </noscript>
        <table class="gsc-search-box" cellpadding="0" cellspacing="0">
          <tbody>
            <tr>
              <td class="gsc-input">
                <input id="gsearchInput" type="text" name="q" maxlength="2048" class="gsc-input" autocomplete="off" title="Google Code Search" style="width:345px"/>
              </td>
              <td class="gsc-search-button">
                <div id="cs-searchresults" onclick="event.cancelBubble = true;"></div>
                <input title="Search" id="gsearchButton" class="gsc-search-button" name="sa" value="Search" type="submit"/>
              </td>
            </tr>
            <tr>
              <td colspan="2" class="greytext">e.g. "templates" or "datastore"</td>
            </tr>
          </tbody>
        </table>
      </form>
    </div> <!-- end searchForm -->
  </div> <!-- end search -->
</div> <!-- end gc-header -->


<div id="codesiteContent">

<a name="gc-topnav-anchor"></a>
<div id="gc-topnav">
  <h1>Google App Engine</h1>
  <ul id="docs" class="gc-topnav-tabs">

    <li id="home_link">
      <a href="../../../index.html" title="Google App Engine home page">Home</a>
    </li>
  
    <li id="docs_link">
      <a href="../../index.html" class="selected" title="Official Google App Engine documentation">Docs</a>
    </li>
  
    <li id="faq_link">
      <a href="../../../kb/index.html" title="Answers to frequently asked questions about Google App Engine">FAQ</a>
    </li>
  
    <li id="articles_link">
      <a href="../../../articles/index.html" title="Focused articles and tutorials for Google App Engine developers">Articles</a>
    </li>
  
    <li>
      <a href="http://googleappengine.blogspot.com/" title="Official Google App Engine blog">Blog</a>
    </li>
  
    <li>
      <a href="../../../community.html" title="Community home for Google App Engine">Community</a>
    </li>
  
    <li>
      <a href="../../../terms.html" title="Google App Engine terms of service">Terms</a>
    </li>
  
    <li>
      <a href="../../../downloads.html" title="Download Google App Engine">Download</a>
    </li>
  

  </ul>
</div> <!-- end gc-topnav -->

    <div class="g-section g-tpl-170">

      <div class="g-unit g-first" id="gc-toc">
        <ul>
  <li><a href="../../../downloads.html">Downloads</a></li>
  <li><a href="http://code.google.com/status/appengine">System Status</a></li>
  <li><a href="http://code.google.com/p/googleappengine/issues/list">Issue Tracker</a></li>
</ul>
<div class="line"></div>
<ul>
  <li><h2>Getting Started</h2>
    <ul>
      <li><a href="../../whatisgoogleappengine.html">What Is Google App Engine?</a></li>
      <li><a href="../../java/gettingstarted/index.html">Java</a>
        <ul>
              <li><a href="../../java/gettingstarted/introduction.html">Introduction</a></li>
    <li><a href="../../java/gettingstarted/installing.html">Installing the Java SDK</a></li>
    <li><a href="../../java/gettingstarted/creating.html">Creating a Project</a></li>
    <li><a href="../../java/gettingstarted/usingusers.html">Using the Users Service</a></li>
    <li><a href="../../java/gettingstarted/usingjsps.html">Using JSPs</a></li>
    <li><a href="../../java/gettingstarted/usingdatastore.html">Using the Datastore with JDO</a></li>
    <li><a href="../../java/gettingstarted/staticfiles.html">Using Static Files</a></li>
    <li><a href="../../java/gettingstarted/uploading.html">Uploading Your Application</a></li>

        </ul>
      </li>
      <li><a href="../gettingstarted/index.html">Python</a>
        <ul>
            <li><a href="../gettingstarted/introduction.html">Introduction</a></li>
  <li><a href="../gettingstarted/devenvironment.html">The Development Environment</a></li>
  <li><a href="../gettingstarted/helloworld.html">Hello, World!</a></li>
  <li><a href="../gettingstarted/usingwebapp.html">Using the webapp Framework</a></li>
  <li><a href="../gettingstarted/usingusers.html">Using the Users Service</a></li>
  <li><a href="../gettingstarted/handlingforms.html">Handling Forms With webapp</a></li>
  <li><a href="../gettingstarted/usingdatastore.html">Using the Datastore</a></li>
  <li><a href="../gettingstarted/templates.html">Using Templates</a></li>
  <li><a href="../gettingstarted/staticfiles.html">Using Static Files</a></li>
  <li><a href="../gettingstarted/uploading.html">Uploading Your Application</a></li>

        </ul>
      </li>
    </ul>
  </li>
</ul>
<div class="line"></div>
<ul>
  <li><h2>Java <sup class="new">Early Look</sup></h2>
    <ul>
          <li><a href="../../java/overview.html">Overview</a></li>
    <li><a href="../../java/runtime.html">Servlet Environment</a></li>
    <li><a href="../../java/datastore/index.html">Storing Data</a>
      <ul>
            <li><a href="../../java/datastore/overview.html">Overview</a></li>
    <li><a href="../../java/datastore/usingjdo.html">Using JDO</a></li>
    <li><a href="../../java/datastore/dataclasses.html">Defining Data Classes</a></li>
    <li><a href="../../java/datastore/creatinggettinganddeletingdata.html">Creating, Getting and Deleting Data</a></li>
    <li><a href="../../java/datastore/queriesandindexes.html">Queries and Indexes</a></li>
    <li><a href="../../java/datastore/transactions.html">Transactions</a></li>
    <li><a href="../../java/datastore/relationships.html">Relationships</a></li>
    <li><a href="../../java/datastore/usingjpa.html">Using JPA</a></li>
    <li><a href="../../java/javadoc/com/google/appengine/api/datastore/package-summary.html">Low-level API</a></li>

      </ul>
    </li>
    <li><a href="../../java/apis.html">Services</a>
      <ul>
        <li><a href="../../java/memcache/index.html">Memcache</a>
          <ul>
                <li><a href="../../java/memcache/overview.html">Overview</a></li>
    <li><a href="../../java/memcache/usingjcache.html">Using JCache</a></li>
    <li><a href="../../java/javadoc/com/google/appengine/api/memcache/package-summary.html">Low-level API</a></li>

          </ul>
        </li>
        <li><a href="../../java/urlfetch/index.html">URL Fetch</a>
          <ul>
                <li><a href="../../java/urlfetch/overview.html">Overview</a></li>
    <li><a href="../../java/urlfetch/usingjavanet.html">Using java.net</a></li>
    <li><a href="../../java/javadoc/com/google/appengine/api/urlfetch/package-summary.html">Low-level API</a></li>

          </ul>
        </li>
        <li><a href="../../java/mail/index.html">Mail</a>
          <ul>
                <li><a href="../../java/mail/overview.html">Overview</a></li>
    <li><a href="../../java/mail/usingjavamail.html">Using JavaMail</a></li>
    <li><a href="../../java/javadoc/com/google/appengine/api/mail/package-summary.html">Low-level API</a></li>

          </ul>
        </li>
        <li><a href="../../java/images/index.html">Images</a>
          <ul>
                <li><a href="../../java/images/overview.html">Overview</a></li>
    <li><a href="../../java/javadoc/com/google/appengine/api/images/package-summary.html">API Reference</a></li>

          </ul>
        </li>
        <li><a href="../../java/users/index.html">Google Accounts</a>
          <ul>
                <li><a href="../../java/users/overview.html">Overview</a></li>
    <li><a href="../../java/javadoc/com/google/appengine/api/users/package-summary.html">API Reference</a></li>

          </ul>
        </li>
      </ul>
    </li>
    <li><a href="../../java/javadoc/index.html">Javadoc Reference</a></li>
    <li><a href="../../java/jrewhitelist.html">JRE Class White List</a></li>
    <li><a href="../../java/config/index.html">Configuration</a>
      <ul>
            <li><a href="../../java/config/webxml.html">Deployment Descriptor</a></li>
    <li><a href="../../java/config/appconfig.html">App Config</a></li>
    <li><a href="../../java/config/indexconfig.html">Index Config</a></li>
    <li><a href="../../java/config/cron.html">Scheduled Tasks</a></li>

      </ul>
    </li>
    <li><a href="../../java/tools/index.html">Tools</a>
      <ul>
            <li><a href="../../java/tools/devserver.html">Development Server</a></li>
    <li><a href="../../java/tools/uploadinganapp.html">Uploading and Managing</a></li>
    <li><a href="../../java/tools/eclipse.html">Google Plugin for Eclipse</a></li>
    <li><a href="../../java/tools/ant.html">Using Apache Ant</a></li>

      </ul>
    </li>
    <li><a href="../../java/howto/index.html">How-To</a>
      <ul>
              <li><a href="../../java/howto/unittesting.html">Unit Testing</a></li>

      </ul>
    </li>

    </ul>
  </li>
</ul>
<div class="line"></div>
<ul>
  <li><h2>Python</h2>
    <ul>
          <li><a href="../overview.html">Overview</a></li>
    <li><a href="../runtime.html">CGI Environment</a></li>
    <li><a href="index.html">Storing Data</a>
      <ul>
             <li><a href="overview.html">Overview</a></li>
     <li><a href="entitiesandmodels.html">Entities and Models</a></li>
     <li><a href="creatinggettinganddeletingdata.html">Creating, Getting and Deleting Data</a></li>
     <li><a href="keysandentitygroups.html">Keys and Entity Groups</a></li>
     <li><a href="queriesandindexes.html">Queries and Indexes</a></li>
     <li><a href="transactions.html">Transactions</a></li>
     <li><a href="typesandpropertyclasses.html">Types and Property Classes</a></li>
     <li><a href="gqlreference.html">GQL Reference</a></li>

     <li><span class="tlw-title tlw-expanded">Reference</span>
       <ul>
         <li><a href="modelclass.html">Model</a></li>
         <li><a href="expandoclass.html">Expando</a></li>
         <li><a href="polymodelclass.html">PolyModel</a></li>
         <li><a href="propertyclass.html">Property</a></li>
         <li><a href="queryclass.html">Query</a></li>
         <li><a href="gqlqueryclass.html">GqlQuery</a></li>
         <li><a href="keyclass.html">Key</a></li>
         <li><a href="functions.html">Functions</a></li>
         <li><a href="exceptions.html">Exceptions</a></li>
       </ul>
     </li>

      </ul>
    </li>
    <li><a href="../apis.html">Services</a>
      <ul>
        <li><a href="../memcache/index.html">Memcache</a>
          <ul>
                 <li><a href="../memcache/overview.html">Overview</a></li>
      <li><a href="../memcache/usingmemcache.html">Using Memcache</a></li>
     <li><span class="tlw-title tlw-expanded">Reference</span>
       <ul>
         <li><a href="../memcache/clientclass.html">Client</a></li>
         <li><a href="../memcache/functions.html">Functions</a></li>
       </ul>
     </li>

          </ul>
        </li>
        <li><a href="../urlfetch/index.html">URL Fetch</a>
          <ul>
                 <li><a href="../urlfetch/overview.html">Overview</a></li>
     <li><span class="tlw-title tlw-expanded">Reference</span>
       <ul>
         <li><a href="../urlfetch/fetchfunction.html">The fetch Function</a></li>
         <li><a href="../urlfetch/responseobjects.html">Response Objects</a></li>
         <li><a href="../urlfetch/exceptions.html">Exceptions</a></li>
       </ul>
     </li>

          </ul>
        </li>
        <li><a href="../mail/index.html">Mail</a>
          <ul>
                 <li><a href="../mail/overview.html">Overview</a></li>
     <li><a href="../mail/sendingmail.html">Sending Mail</a></li>
     <li><a href="../mail/attachments.html">Attachments</a></li>
     <li><span class="tlw-title tlw-expanded">Reference</span>
       <ul>
         <li><a href="../mail/emailmessageclass.html">EmailMessage</a></li>
         <li><a href="../mail/emailmessagefields.html">Message Fields</a></li>
         <li><a href="../mail/functions.html">Functions</a></li>
         <li><a href="../mail/exceptions.html">Exceptions</a></li>
       </ul>
     </li>

          </ul>
        </li>
        <li><a href="../images/index.html">Images</a>
          <ul>
                 <li><a href="../images/overview.html">Overview</a></li>
     <li><a href="../images/installingPIL.html">Installing PIL</a></li>
     <li><a href="../images/usingimages.html">Using the Images API</a></li>
     <li><span class="tlw-title tlw-expanded">Reference</span>
       <ul>
         <li><a href="../images/imageclass.html">Image</a></li>
         <li><a href="../images/functions.html">Functions</a></li>
         <li><a href="../images/exceptions.html">Exceptions</a></li>
       </ul>
     </li>

          </ul>
        </li>
        <li><a href="../users/index.html">Google Accounts</a>
          <ul>
                 <li><a href="../users/overview.html">Overview</a></li>
     <li><a href="../users/userobjects.html">User Objects</a></li>
     <li><a href="../users/loginurls.html">Login URLs</a></li>
     <li><a href="../users/adminusers.html">Admin Users</a></li>

     <li><span class="tlw-title tlw-expanded">Reference</span>
       <ul>
         <li><a href="../users/userclass.html">User</a></li>
         <li><a href="../users/functions.html">Functions</a></li>
         <li><a href="../users/exceptions.html">Exceptions</a></li>
       </ul>
     </li>

          </ul>
        </li>
      </ul>
    </li>
    <li><a href="../config/index.html">Configuration</a>
      <ul>
            <li><a href="../config/appconfig.html">App Config</a></li>
    <li><a href="../config/indexconfig.html">Index Config</a></li>
    <li><a href="../config/cron.html">Scheduled Tasks</a></li>

      </ul>
    </li>
    <li><a href="../tools/index.html">Tools</a>
      <ul>
            <li><a href="../tools/devserver.html">Development Server</a></li>
    <li><a href="../tools/uploadinganapp.html">Uploading and Managing</a></li>
    <li><a href="../tools/uploadingdata.html">Uploading Data</a></li>
    <li><a href="../tools/webapp/index.html">webapp Framework</a>
      <ul>
             <li><a href="../tools/webapp/overview.html">Overview</a></li>
     <li><a href="../tools/webapp/running.html">Running the Application</a></li>
     <li><a href="../tools/webapp/requesthandlers.html">Request Handlers</a></li>
     <li><a href="../tools/webapp/requestdata.html">Request Data</a></li>
     <li><a href="../tools/webapp/buildingtheresponse.html">Building the Response</a></li>
     <li><a href="../tools/webapp/redirects.html">Redirects, Headers and Status Codes</a></li>
     

     <li><span class="tlw-title tlw-expanded">Reference</span>
       <ul>
         <li><a href="../tools/webapp/requestclass.html">Request</a></li>
         <li><a href="../tools/webapp/responseclass.html">Response</a></li>
         <li><a href="../tools/webapp/requesthandlerclass.html">RequestHandler</a></li>
         <li><a href="../tools/webapp/wsgiapplicationclass.html">WSGIApplication</a></li>
         <li><a href="../tools/webapp/utilmodule.html">Utility Functions</a></li>
         
       </ul>
     </li>

      </ul>
    </li>
    <li><a href="../tools/libraries.html">Third-party Libraries</a></li>

      </ul>
    </li>
    <li><a href="../howto/index.html">How-To</a>
      <ul>
              <li><a href="../howto/usinggdataservices.html">Google Data Services</a></li>

      </ul>
    </li>

    </ul>
  </li>
</ul>
<div class="line"></div>
<ul>
  <li><h2>Managing Your App</h2>
    <ul>
      <li><a href="../../theadminconsole.html">The Admin Console</a></li>
      <li><a href="../../quotas.html">Quotas</a></li>
      <li><a href="../../billing.html">Billing</a></li>
    </ul>
  </li>
</ul>
<div class="line"></div>
<ul>
  <li><h2>Resources</h2>
    <ul>
      <li><a href="../../../kb/index.html">FAQ</a></li>
      <li><a href="../../../articles/index.html">Articles</a></li>
      <li><a href="http://appengine-cookbook.appspot.com/">Cookbook</a></li>
      <li><a href="http://appgallery.appspot.com/">App Gallery</a></li>
      <li><a href="http://code.google.com/p/googleappengine/">SDK Code</a></li>
      <li><a href="http://code.google.com/p/google-app-engine-samples/">Sample Apps Code</a></li>
      <li><a href="../../../community.html">Discussion Groups</a></li>
    </ul>
  </li>
</ul>
<div class="line"></div>
<ul>
  <li><a href="../../roadmap.html">Product Roadmap</a></li>
  <li><a href="http://code.google.com/p/googleappengine/wiki/SdkReleaseNotes">Release Notes</a></li>
  <li><a href="../../revision_history.html">Revision History</a></li>
</ul>

        <a class="hidden" href="#gc-topnav-anchor">More Google App Engine resource links</a>
      </div>

      <div class="g-unit" id="gc-pagecontent">
        <h1 class="page_title">Queries and Indexes</h1>


















<p>Every datastore query uses an index, a table that contains the results for the query in the desired order.  An App Engine application defines its indexes in a configuration file named <code>index.yaml</code>.  The development web server automatically adds suggestions to this file as it encounters queries that do not yet have indexes configured.  You can tune indexes manually by editing the file before uploading the application.</p>

<p>The index-based query mechanism supports most common kinds of queries, but it does not support some queries you may be used to from other database technologies.  Restrictions on queries, and their explanations, are described below.</p>

<ul>
  <li><a href="#Introducing_Queries">Introducing Queries</a></li>
  <li><a href="#Introducing_Indexes">Introducing Indexes</a></li>
  <li><a href="#Defining_Indexes_With_Configuration">Defining Indexes With Configuration</a></li>
  <li><a href="#Queries_on_Keys">Queries on Keys</a></li>
  <li><a href="#Restrictions_on_Queries">Restrictions on Queries</a></li>
  <li><a href="#Big_Entities_and_Exploding_Indexes">Big Entities and Exploding Indexes</a></li>
</ul>


<h2 id="Introducing_Queries">Introducing Queries</h2>

<p>A query retrieves entities from the datastore that meet a set of conditions.  The query specifies an entity kind, zero or more conditions based on entity property values (sometimes called "filters"), and zero or more sort order descriptions.  When the query is executed, it fetches all entities of the given kind that meet all of the given conditions, sorted in the order described.</p>

<p>A query can also return <a href="#Queries_on_Keys">just the keys</a> of the result entities instead of the entities themselves.</p>


<p>The datastore Python API provides two interfaces for preparing and executing queries: the <a href="queryclass.html">Query</a> interface, which uses methods to prepare the query, and the <a href="gqlqueryclass.html">GqlQuery</a> interface, which uses a SQL-like query language called GQL to prepare the query from a query string.  These interfaces are described in more detail in <a href="creatinggettinganddeletingdata.html#Getting_Entities_Using_a_Query">Creating, Getting and Deleting Data: Getting Entities Using a Query</a> and the corresponding reference pages.</p>

<pre class="prettyprint">
class Person(db.Model):
  first_name = db.StringProperty()
  last_name = db.StringProperty()
  city = db.StringProperty()
  birth_year = db.IntegerProperty()
  height = db.IntegerProperty()

# The Query interface prepares a query using instance methods.
q = Person.all()
q.filter("last_name =", "Smith")
q.filter("height <", 72)
q.order("-height")

# The GqlQuery interface prepares a query using a GQL query string.
q = db.GqlQuery("SELECT * FROM Person " + 
                "WHERE last_name = :1 AND height < :2 " +
                "ORDER BY height DESC",
                "Smith", 72)

# The query is not executed until results are accessed.
results = q.fetch(5)
for p in results:
  print "%s %s, %d inches tall" % (p.first_name, p.last_name, p.height)
</pre>

<p>A filter includes a property name, a comparison operator, and a value.  An entity passes the filter if it has a property of the given name and its value compares to the given value as described by the operator.  The entity is a result for the query if it passes all of its filters.</p>

<p>The filter operator can be any of the following:</p>
<ul>
  <li><code>&lt;</code> less than</li>
  <li><code>&lt;=</code> less than or equal to</li>
  <li><code>=</code> equal to</li>
  <li><code>&gt;</code> greater than</li>
  <li><code>&gt;=</code> greater than or equal to</li>
  <li><code>!=</code> not equal to</li>
  <li><code>IN</code> equal to any of the values in the provided list</li>
</ul>

<p>The <code>!=</code> operator actually performs 2 queries: one where all other filters are the same and the not-equal filter is replaced with a less-than filter, and one where the not-equal filter is replaces with a greater-than filter.  The results are merged, in order.  As described below in the discussion of inequality filters, a query can only have one not-equal filter, and such a query cannot have other inequality filters.</p>

<p>The <code>IN</code> operator also performs multiple queries, one for each item in the provided list value where all other filters are the same and the <code>IN</code> filter is replaces with an equal-to filter.  The results are merged, in the order of the items in the list.  If a query has more than <code>IN</code> filter, the query is performed as multiple queries, one for each combination of values in the <code>IN</code> filters.</p>

<p>A single query containing <code>!=</code> and <code>IN</code> operators is limited to 30 sub-queries.</p> 



<h2 id="Introducing_Indexes">Introducing Indexes</h2>

<p>The App Engine datastore maintains an index for every query an application intends to make.  As the application makes changes to datastore entities, the datastore updates the indexes with the correct results.  When the application executes a query, the datastore fetches the results directly from the corresponding index.</p>

<p>An application has an index for each combination of kind, filter property and operator, and sort order used in a query.  Consider the example query, stated in GQL:</p>


<pre>
SELECT * FROM Person WHERE last_name = "Smith"
                       AND height &lt; 72
                  ORDER BY height DESC
</pre>



<p>The index for this query is a table of keys for entities of the kind <code>Person</code>, with columns for the values of the <code>height</code> and <code>last_name</code> properties.  The index is sorted by <code>height</code> in descending order.</p>

<p>Two queries of the same form but with different filter values use the same index.  For example, the following query uses the same index as the query above:</p>


<pre>
SELECT * FROM Person WHERE last_name = "Jones"
                       AND height &lt; 63
                     ORDER BY height DESC
</pre>



<p>The datastore executes a query using the following steps:</p>

<ol>
  <li>The datastore identifies the index that corresponds with the query's kind, filter properties, filter operators, and sort orders.</li>
  <li>The datastore starts scanning the index at the first entity that meets all of the filter conditions using the query's filter values.</li>
  <li>The datastore continues to scan the index, returning each entity, until it finds the next entity that does not meet the filter conditions, until it reaches the end of the index, or until it has collected the maximum number of results requested by the query.</li>
</ol>

<p>An index table contains columns for every property used in a filter or sort order.  The rows are sorted by the following aspects, in order:</p>

<ul>
  <li>ancestors</li>
  <li>property values used in equality filters</li>
  <li>property values used in inequality filters</li>
  <li>property values used in sort orders</li>
</ul>


<p class="note"><b>Note: </b>For the purposes of indexes, <code>IN</code> filters are handled like <code>=</code> filters, and <code>!=</code> filters are handled like the other inequality filters.</p>


<p>This puts all results for every possible query that uses this index in consecutive rows in the table.</p>


<p class="note"><b>Tip:</b> Query filters do not have an explicit way to match just part of a string value, but you can fake a prefix match using inequality filters:<br /><br />
<code>db.GqlQuery("SELECT * FROM MyModel WHERE prop >= :1 AND prop < :2", "abc", u"abc" + u"\ufffd")</code><br /><br />
This matches every <code>MyModel</code> entity with a string property <code>prop</code> that begins with the characters <code>abc</code>.  The unicode string <code>u"\ufffd"</code> represents the largest possible Unicode character.  When the property values are sorted in an index, the values that fall in this range are all of the values that begin with the given prefix.</p>



<p>This mechanism supports a wide range of queries and is suitable for most applications.  However, it does not support some kinds of queries you may be used to from other database technologies.</p>


<h3>Entities Without a Filtered Property Are Never Returned by a Query</h3>

<p>An index only contains entities that have every property referred to by the index.  If an entity does not have a property referred to by an index, the entity will not appear in the index, and will never be a result for the query that uses the index.</p>

<p>Note that the App Engine datastore makes a distinction between an entity that does not possess a property and an entity that possesses the property with a null value (<code>None</code>).  If you want every entity of a kind to be a potential result for a query, you can use a data model that assigns a default value (such as <code>None</code>) to properties used by query filters.</p>


<h3>Properties that Aren't Indexed</h3>

<p>Property values that aren't indexed are not findable by queries. This includes properties that are marked as not indexed, as well as properties with values of the long text value type (Text) or the long binary value type (Blob).</p>

<p>A query with a filter or sort order on a property will never match an entity whose value for the property is a Text or Blob, or which was written with that property marked as not indexed.  Properties with such values behave as if the property is not set with regard to query filters and sort orders.</p>


<h3>Property Values of Mixed Types are Ordered By Type</h3>

<p>When two entities have properties of the same name but of different value types, an index of the property sorts the entities first by value type, then by an order appropriate to the type.  For example, if two entities each have a property named "age," one with an integer value and one with a string value, the entity with the integer value will always appear before the entity with the string value when sorted by the "Age" property, regardless of the values themselves.</p>

<p>This is especially worth noting in the case of integers and floating point numbers, which are treated as separate types by the datastore.  A property with the integer value <code>38</code> is sorted before a property with the floating point value <code>37.5</code>, because all integers are sorted before floats.</p>




<h2 id="Defining_Indexes_With_Configuration">Defining Indexes With Configuration</h2>

<p>App Engine builds indexes for several simple queries by default.  For other queries, the application must specify the indexes it needs in a configuration file named <code>index.yaml</code>.  If the application running under App Engine tries to perform a query for which there is no corresponding index (either provided by default or described in <code>index.yaml</code>), the query will fail.</p>

<p>App Engine provides automatic indexes for the following forms of queries:</p>
<ul>
  <li>queries using only equality and ancestor filters</li>
  <li>queries using only inequality filters (which can only be of a single property)</li>
  <li>queries with only one sort order on a property, either ascending or descending</li>
</ul>

<p>Other forms of queries require their indexes to be specified in <code>index.yaml</code>, including:</p>
<ul>
  <li>queries with multiple sort orders</li>
  <li>queries with a sort order on keys in descending order</li>
  <li>queries with one or more inequality filters on a property and one or more equality filters over other properties</li>
  <li>queries with inequality filters and ancestor filters</li>
</ul>

<p>The development web server makes managing index configuration easy: Instead of failing to execute a query that does not have an index and requires it, the development web server can generate configuration for an index that would allow the query to succeed.  If your local testing of your application calls every possible query the application will make (every combination of kind, ancestor, filter and sort order), the generated entries will represent a complete set of indexes.  If your testing might not exercise every possible query form, you can review and adjust the index configuration before uploading the application.</p>


<p>App Engine builds indexes for several simple queries by default.  For other queries, the application must specify the indexes it needs in a configuration file named <code>index.yaml</code>.  If the application running under App Engine tries to perform a query for which there is no corresponding index (either provided by default or described in <code>index.yaml</code>), the query will fail.</p>

<p><code>index.yaml</code> describes each index table, including the kind, the properties needed for the query filters and sort orders, and whether or not the query uses an ancestor clause (either <a href="queryclass.html#Query_ancestor">Query.ancestor()</a> or a GQL <a href="gqlreference.html">ANCESTOR IS</a> clause).  The properties are listed in the order they are to be sorted: properties used in equality or <code>IN</code> filters first, followed by the property used in inequality filters, then the query results sort orders and their directions.</p>

<p>Consider once again the following example query:</p>

<pre>
SELECT * FROM Person WHERE last_name = "Smith"
                       AND height < 72
                     ORDER BY height DESC
</pre>

<p>If the application executed only this query (and possibly other queries similar to this one but with different values for <code>"Smith"</code> and <code>72</code>), the <code>index.yaml</code> file would look like this:</p>

<pre>
indexes:
- kind: Person
  properties:
  - name: last_name
  - name: height
    direction: desc
</pre>

<p>When an entity is created or updated, every appropriate index is updated as well.  The number of indexes that apply to an entity affects the time it takes to create or update the entity.</p>

<p>For more information on the syntax of <code>index.yaml</code>, see <a href="../config/indexconfig.html">Configuring Indexes</a>.</p>



<h2 id="Queries_on_Keys">Queries on Keys</h2>


<p>Entity keys can be the subject of a query filter or sort order, using the special name <code>__key__</code> in place of the property name.  The datastore considers the complete key value for such queries, including the entity's parent path, the kind, and the app-assigned key name string or system-assigned numeric ID.</p>

<p>A query can return entity keys instead of full entities. You can trigger this behavior by passing <code>keys_only=True</code> to <a href="queryclass.html">Query</a>, or by using <code>SELECT __key__</code> in <a href="gqlreference.html">GQL</a>.

<p class="note"><b>Note:</b> Queries that return keys are faster and cost less CPU than queries that return entities, since the keys themselves are already in the index, so the query doesn't need to fetch the actual entities. If you only need the keys from your query results &mdash; for example, if you're just going to delete the results &mdash; consider using a keys only query.</p>

<p>Because an entity key is unique across all entities in the system, <code>__key__</code> queries make it easy to retrieve entities of a given kind in batches, such as for a batch dump of the contents of the datastore.  Unlike <a href="gqlqueryclass.html#GqlQuery_fetch">offset</a>, this works efficiently for any number of entities.  For example:</p>

<pre class="prettyprint">
class MainHandler(webapp.RequestHandler):
  def get(self):
    query = Entity.gql('ORDER BY __key__')

    # Use a query parameter to keep track of the last key of the last
    # batch, to know where to start the next batch.
    last_key_str = self.request.get('last')
    if last_key_str:
      last_key = db.Key(last_key_str)
      query = Entity.gql('WHERE __key__ > :1 ORDER BY __key__', last_key)

    # For batches of 20, fetch 21, then use result #20 as the "last"
    # if there is a 21st.
    entities = query.fetch(21)
    new_last_key_str = None
    if len(entities) == 21:
      new_last_key_str = str(entities[19].key())

    # Return the data and new_last_key_str.  Client would use
    # http://...?last=new_last_key_str to fetch the next batch.
    # ...
</pre>


<p>Keys are ordered first by parent path, then by kind, then by key name or ID.  Kinds and key names are strings and are ordered by byte value.  IDs are integers and are ordered numerically.  If entities of the same parent and kind use a mix of key name strings and numeric IDs, entities with numeric IDs are considered to be less than entities with key name strings.  Elements of the parent path are compared similarly: by kind (string), then by key name (string) or ID (number).</p>

<p>Queries involving keys use indexes just like queries involving properties, with one minor difference: Unlike with a property, a query with an equality filter on the key that also has additional filters must use a custom index defined in the app's index configuration file.  As with all queries, the development web server creates appropriate configuration entries in this file when a query that needs a custom index is tested.</p>


<h2 id="Restrictions_on_Queries">Restrictions on Queries</h2>

<p>The nature of the index query mechanism imposes a few restrictions on what a query can do.</p>

<h3 id="Filtering_Or_Sorting_On_a_Property_Requires_That_the_Property_Exists">
 Filtering Or Sorting On a Property Requires That the Property Exists</h3>

<p>A query filter condition or sort order for a property also implies a condition that the entity have a value for the property.</p>

<p>A datastore entity is not required to have a value for a property that other entities of the same kind have.  A filter on a property can only match an entity with a value for the property.  Entities without a value for a property used in a filter or sort order are omitted from the index built for the query.</p>


<h3 id="No_Filter_That_Matches_Entities_That_Do_Not_Have_a_Property">
 No Filter That Matches Entities That Do Not Have a Property</h3>

<p>It is not possible to perform a query for entities that are missing a given property.  One alternative is to create a fixed (modeled) property with a default value of <code>None</code>, then create a filter for entities with <code>None</code> as the property value.</p>


<h3 id="Inequality_Filters_Are_Allowed_On_One_Property_Only">
 Inequality Filters Are Allowed On One Property Only</h3>

<p>A query may only use inequality filters (<code>&lt;</code>, <code>&lt;=</code>, <code>&gt;=</code>, <code>&gt;</code>, <code>!=</code>) on one property across all of its filters.</p>

<p>For example, this query is allowed:</p>


<pre>
SELECT * FROM Person WHERE birth_year &gt;= :min
                       AND birth_year &lt;= :max
</pre>



<p>However, this query is <i>not</i> allowed, because it uses inequality filters on two different properties in the same query:</p>


<pre>
SELECT * FROM Person WHERE birth_year &gt;= :min_year
                       AND height &gt;= :min_height     # ERROR
</pre>



<p>Filters can combine equal (<code>=</code>) comparisons for different properties in the same query, including queries with one or more inequality conditions on a property.  This is allowed:</p>


<pre>
SELECT * FROM Person WHERE last_name = :last_name
                       AND city = :city
                       AND birth_year &gt;= :min_year
</pre>



<p>The query mechanism relies on all results for a query to be adjacent to one another in the index table, to avoid having to scan the entire table for results.  A single index table cannot represent multiple inequality filters on multiple properties while maintaining that all results are consecutive in the table.</p>


<h3 id="Properties_In_Inequality_Filters_Must_Be_Sorted_Before_Other_Sort_Orders">
 Properties In Inequality Filters Must Be Sorted Before Other Sort Orders</h3>

<p>If a query has both a filter with an inequality comparison and one or more sort orders, the query must include a sort order for the property used in the inequality, and the sort order must appear <i>before</i> sort orders on other properties.</p>

<p>This query is <i>not</i> valid, because it uses an inequality filter and does not order by the filtered property:</p>

<pre>
SELECT * FROM Person WHERE birth_year &gt;= :min_year
                     ORDER BY last_name              # ERROR
</pre>



<p>Similarly, this query is not valid because it does not order by the filtered property before ordering by other properties:</p>

<pre>
SELECT * FROM Person WHERE birth_year &gt;= :min_year
                     ORDER BY last_name, birth_year  # ERROR
</pre>



<p>This query is valid:</p>

<pre>
SELECT * FROM Person WHERE birth_year &gt;= :min_year
                     ORDER BY birth_year, last_name
</pre>



<p>To get all results that match an inequality filter, a query scans the index table for the first matching row, then returns all consecutive results until it finds a row that doesn't match.  For the consecutive rows to represent the complete result set, the rows must be ordered by the inequality filter before other sort orders.</p>


<h3 id="Sort_Orders_and_Properties_With_Multiple_Values">Sort Orders and Properties With Multiple Values</h3>

<p>Due to the way properties with multiple values are indexed, the sort order for these properties is unusual:</p>

<ul class="doublespace">
  <li>If the entities are sorted by a multi-valued property in ascending order, the value used for ordering is the smallest value.</li>
  <li>If the entities are sorted by a multi-valued property in descending order, the value used for ordering is the greatest value.</li>
  <li>Other values do not affect the sort order, nor does the number of values.</li>
  <li>In the case of a tie, the key of the entity is used as the tie-breaker.</li>
</ul>

<p>This sort order has the unusual consequence that <code>[1, 9]</code> comes before <code>[4, 5, 6, 7]</code> in both ascending <i>and</i> descending order.</p>

<p>One important caveat is queries with both an equality filter and a sort
order on a multi-valued property. In those queries, the sort order is disregarded.
For single-valued properties, this is a simple optimization. Every result would
have the same value for the property, so the results do not need to be sorted
further.</p>

<p>However, multi-valued properties may have additional values. Since the sort order
is disregarded, the query results may be returned in a different order than if
the sort order were applied. (Restoring the dropped sort order would be
expensive and require extra indices, and this use case is rare, so the query
planner leaves it off.)</p>


<h2 id="Big_Entities_and_Exploding_Indexes">Big Entities and Exploding Indexes</h2>

<p>As described above, every property (that doesn't have a Text or Blob value) of every entity is added to at least one index table, including a simple index provided by default, and any indexes described in the application's <code>index.yaml</code> file that refer to the property.  For an entity that has one value for each property, App Engine stores a property value once in its simple index, and once for each time the property is referred to in a custom index.  Each of these index entries must be updated every time the value of the property changes, so the more indexes that refer to the property, the more time it will take to update the property.</p>

<p>To prevent the update of an entity from taking too long, the datastore limits the number of index entries that a single entity can have.  The limit is large, and most applications will not notice.  However, there are some circumstances where you might encounter the limit.  For example, an entity with very many single-value properties can exceed the index entry limit.</p>

<p>Properties with multiple values store each value as a separate entry in an index.  An entity with a single property with very many values can exceed the index entry limit.</p>

<p>Custom indexes that refer to multiple properties with multiple values can get very large with only a few values.  To completely record such properties, the index table must include a row for <i>every permutation of the values</i> of every property for the index.</p>


<p>For example, the following index (described in <code>index.yaml</code> syntax) includes the <code>x</code> and <code>y</code> properties for entities of the kind <code>MyModel</code>:</p>

<pre class="prettyprint">
indexes:
- kind: MyModel
  properties:
  - name: x
  - name: y
</pre>

<p>The following code creates an entity with 2 values for the property <code>x</code> and 2 values for the property <code>y</code>:</p>

<pre class="prettyprint">
class MyModel(db.Expando):
  pass

e2 = MyModel()
e2.x = ['red', 'blue']
e2.y = [1, 2]
e2.put()
</pre>




<p>To accurately represent these values, the index must store 12 property values: 2 each for the built-in indexes on <code>x</code> and <code>y</code>, and 2 for each of the 4 permutations of <code>x</code> and <code>y</code> in the custom index.  With many values of multi-valued properties, this can mean an index must store very many index entries for a single entity.  You could call an index that refers to multiple properties with multiple values an "exploding index," because it can get very large with just a few values.</p>

<p>If a <code>put()</code> would result in a number of index entries that exceeds the limit, the call will fail with an exception.  If you create a new index that would contain a number of index entries that exceeds the limit for any entity when built, queries against the index will fail, and the index will appear in the "Error" state in the Admin Console.</p>


<p>To handle "Error" indexes, first remove them from your <code>index.yaml</code> file and run <code>appcfg.py vacuum_indexes</code>. Then, either reformulate the index definition and corresponding queries or remove the entities that are causing the index to "explode." Finally, add the index back to <code>index.yaml</code> and run <code>appcfg.py update_indexes</code>.</p>


<p>You can avoid exploding indexes by avoiding queries that would require a custom index using a list property.  As described above, this includes queries with multiple sort orders, a mix of equality and inequality filters, and ancestor filters.</p>



      </div><!-- end gc-pagecontent -->
   </div><!-- end gooey wrapper -->

    </div> <!-- end codesite content -->

<div id="gc-footer" dir="ltr">
  <div class="text">
    
      <div class="notice"><div id="notice" style="text-align: center; border: 1em 0em 1em 0em">
  Except as otherwise <a
  href="http://code.google.com/policies.html#restrictions">noted</a>,
  the content of this page is licensed under the <a rel="license"
  href="http://creativecommons.org/licenses/by/2.5/">Creative Commons
  Attribution 2.5 License</a>, and code samples are licensed under the
  <a rel="license" href="http://www.apache.org/licenses/LICENSE-2.0">Apache
  2.0 License</a>.
<!-- <rdf:RDF xmlns="http://web.resource.org/cc/" 
              xmlns:dc="http://purl.org/dc/elements/1.1/"
              xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#">
  <Work rdf:about="">
    <license rdf:resource="http://creativecommons.org/licenses/by/2.5/" />
  </Work>
  <License rdf:about="http://creativecommons.org/licenses/by/2.5/">
    <permits rdf:resource="http://web.resource.org/cc/Reproduction"/>
    <permits rdf:resource="http://web.resource.org/cc/Distribution"/>
    <requires rdf:resource="http://web.resource.org/cc/Notice"/>
    <requires rdf:resource="http://web.resource.org/cc/Attribution"/>
    <permits rdf:resource="http://web.resource.org/cc/DerivativeWorks"/>
  </License>
</rdf:RDF> -->
</div>
Java is a registered trademark of Sun Microsystems, Inc.</div>
    
    &copy;2009 Google -
    <a href="http://code.google.com">Code Home</a> -
    <a href="http://www.google.com/accounts/TOS">Terms of Service</a> -
    <a href="http://www.google.com/privacy.html">Privacy Policy</a> -
    <a href="http://code.google.com/more">Site Directory</a>
    <br /> <br />
    Google Code offered in:
    <a href="http://code.google.com/intl/en">English</a> -
    <a href="http://code.google.com/intl/es">Español</a> -
    <a href="http://code.google.com/intl/ja">日本語</a> -
    <a href="http://code.google.com/intl/ko">한국어</a> -
    <a href="http://code.google.com/intl/pt-BR">Português</a> -
    <a href="http://code.google.com/intl/ru">Pусский</a> -
    <a href="http://code.google.com/intl/zh-CN">中文(简体)</a> -
    <a href="http://code.google.com/intl/zh-TW">中文(繁體)</a>
  </div>
</div><!-- end gc-footer -->

</div><!-- end gc-containter -->

<script type="text/javascript">CODESITE_CSITimer['load'].tick('ats');</script>
<script src="../../../../js/codesite_tail.pack.04102009.js" type="text/javascript"></script>






  </body>
</html>


